use [QLVT]
go
use QLVT

/******************************************************************************************
Create the sa login.
******************************************************************************************/
if not exists(select * from master..syslogins where name = 'sa')
	exec sp_addlogin 'sa', '', 'QLVT'
go

/******************************************************************************************
Grant the sa login access to the QLVT database.
******************************************************************************************/
if not exists (select * from [dbo].sysusers where name = N'sa' and uid < 16382)
	exec sp_grantdbaccess N'sa', N'sa'
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CustomerInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_CustomerInsert]
go

create procedure [dbo].[sp_CustomerInsert]
(
	@Name nvarchar(50),
	@Address nvarchar(50),
	@Active bit,
	@Email nchar(30),
	@Website nchar(30),
	@Phone1 nchar(12),
	@Phone2 nchar(12)
)

as

set nocount on

insert into [Customer]
(
	[Name],
	[Address],
	[Active],
	[Email],
	[Website],
	[Phone1],
	[Phone2]
)
values
(
	@Name,
	@Address,
	@Active,
	@Email,
	@Website,
	@Phone1,
	@Phone2
)

select scope_identity()
go

grant execute on [dbo].[sp_CustomerInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CustomerUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_CustomerUpdate]
go

create procedure [dbo].[sp_CustomerUpdate]
(
	@Id int,
	@Name nvarchar(50),
	@Address nvarchar(50),
	@Active bit,
	@Email nchar(30),
	@Website nchar(30),
	@Phone1 nchar(12),
	@Phone2 nchar(12)
)

as

set nocount on

update [Customer]
set [Name] = @Name,
	[Address] = @Address,
	[Active] = @Active,
	[Email] = @Email,
	[Website] = @Website,
	[Phone1] = @Phone1,
	[Phone2] = @Phone2
where [Id] = @Id
go

grant execute on [dbo].[sp_CustomerUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CustomerDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_CustomerDelete]
go

create procedure [dbo].[sp_CustomerDelete]
(
	@Id int
)

as

set nocount on

delete from [Customer]
where [Id] = @Id
go

grant execute on [dbo].[sp_CustomerDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CustomerSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_CustomerSelect]
go

create procedure [dbo].[sp_CustomerSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[Name],
	[Address],
	[Active],
	[Email],
	[Website],
	[Phone1],
	[Phone2]
from [Customer]
where [Id] = @Id
go

grant execute on [dbo].[sp_CustomerSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CustomerSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_CustomerSelectAll]
go

create procedure [dbo].[sp_CustomerSelectAll]

as

set nocount on

select [Id],
	[Name],
	[Address],
	[Active],
	[Email],
	[Website],
	[Phone1],
	[Phone2]
from [Customer]
go

grant execute on [dbo].[sp_CustomerSelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarterialReturnInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MarterialReturnInsert]
go

create procedure [dbo].[sp_MarterialReturnInsert]
(
	@IdOrderFormMaterial int,
	@ReturnDay datetime,
	@Amount int
)

as

set nocount on

insert into [MarterialReturn]
(
	[IdOrderFormMaterial],
	[ReturnDay],
	[Amount]
)
values
(
	@IdOrderFormMaterial,
	@ReturnDay,
	@Amount
)

select scope_identity()
go

grant execute on [dbo].[sp_MarterialReturnInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarterialReturnUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MarterialReturnUpdate]
go

create procedure [dbo].[sp_MarterialReturnUpdate]
(
	@Id int,
	@IdOrderFormMaterial int,
	@ReturnDay datetime,
	@Amount int
)

as

set nocount on

update [MarterialReturn]
set [IdOrderFormMaterial] = @IdOrderFormMaterial,
	[ReturnDay] = @ReturnDay,
	[Amount] = @Amount
where [Id] = @Id
go

grant execute on [dbo].[sp_MarterialReturnUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarterialReturnDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MarterialReturnDelete]
go

create procedure [dbo].[sp_MarterialReturnDelete]
(
	@Id int
)

as

set nocount on

delete from [MarterialReturn]
where [Id] = @Id
go

grant execute on [dbo].[sp_MarterialReturnDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarterialReturnDeleteAllByIdOrderFormMaterial]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MarterialReturnDeleteAllByIdOrderFormMaterial]
go

create procedure [dbo].[sp_MarterialReturnDeleteAllByIdOrderFormMaterial]
(
	@IdOrderFormMaterial int
)

as

set nocount on

delete from [MarterialReturn]
where [IdOrderFormMaterial] = @IdOrderFormMaterial
go

grant execute on [dbo].[sp_MarterialReturnDeleteAllByIdOrderFormMaterial] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarterialReturnSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MarterialReturnSelect]
go

create procedure [dbo].[sp_MarterialReturnSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[IdOrderFormMaterial],
	[ReturnDay],
	[Amount]
from [MarterialReturn]
where [Id] = @Id
go

grant execute on [dbo].[sp_MarterialReturnSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarterialReturnSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MarterialReturnSelectAll]
go

create procedure [dbo].[sp_MarterialReturnSelectAll]

as

set nocount on

select [Id],
	[IdOrderFormMaterial],
	[ReturnDay],
	[Amount]
from [MarterialReturn]
go

grant execute on [dbo].[sp_MarterialReturnSelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarterialReturnSelectAllByIdOrderFormMaterial]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MarterialReturnSelectAllByIdOrderFormMaterial]
go

create procedure [dbo].[sp_MarterialReturnSelectAllByIdOrderFormMaterial]
(
	@IdOrderFormMaterial int
)

as

set nocount on

select [Id],
	[IdOrderFormMaterial],
	[ReturnDay],
	[Amount]
from [MarterialReturn]
where [IdOrderFormMaterial] = @IdOrderFormMaterial
go

grant execute on [dbo].[sp_MarterialReturnSelectAllByIdOrderFormMaterial] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialInsert]
go

create procedure [dbo].[sp_MaterialInsert]
(
	@Name nvarchar(50),
	@IdUnit int,
	@Description nvarchar(50),
	@PriceByDay int
)

as

set nocount on

insert into [Material]
(
	[Name],
	[IdUnit],
	[Description],
	[PriceByDay]
)
values
(
	@Name,
	@IdUnit,
	@Description,
	@PriceByDay
)

select scope_identity()
go

grant execute on [dbo].[sp_MaterialInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialUpdate]
go

create procedure [dbo].[sp_MaterialUpdate]
(
	@Id int,
	@Name nvarchar(50),
	@IdUnit int,
	@Description nvarchar(50),
	@PriceByDay int
)

as

set nocount on

update [Material]
set [Name] = @Name,
	[IdUnit] = @IdUnit,
	[Description] = @Description,
	[PriceByDay] = @PriceByDay
where [Id] = @Id
go

grant execute on [dbo].[sp_MaterialUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialDelete]
go

create procedure [dbo].[sp_MaterialDelete]
(
	@Id int
)

as

set nocount on

delete from [Material]
where [Id] = @Id
go

grant execute on [dbo].[sp_MaterialDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialDeleteAllByIdUnit]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialDeleteAllByIdUnit]
go

create procedure [dbo].[sp_MaterialDeleteAllByIdUnit]
(
	@IdUnit int
)

as

set nocount on

delete from [Material]
where [IdUnit] = @IdUnit
go

grant execute on [dbo].[sp_MaterialDeleteAllByIdUnit] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialSelect]
go

create procedure [dbo].[sp_MaterialSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[Name],
	[IdUnit],
	[Description],
	[PriceByDay]
from [Material]
where [Id] = @Id
go

grant execute on [dbo].[sp_MaterialSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialSelectAll]
go

create procedure [dbo].[sp_MaterialSelectAll]

as

set nocount on

select [Id],
	[Name],
	[IdUnit],
	[Description],
	[PriceByDay]
from [Material]
go

grant execute on [dbo].[sp_MaterialSelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialSelectAllByIdUnit]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialSelectAllByIdUnit]
go

create procedure [dbo].[sp_MaterialSelectAllByIdUnit]
(
	@IdUnit int
)

as

set nocount on

select [Id],
	[Name],
	[IdUnit],
	[Description],
	[PriceByDay]
from [Material]
where [IdUnit] = @IdUnit
go

grant execute on [dbo].[sp_MaterialSelectAllByIdUnit] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormInsert]
go

create procedure [dbo].[sp_OrderFormInsert]
(
	@IdRepository int,
	@IdCustomer int,
	@RentDay datetime,
	@Vat int,
	@Discount int,
	@Building nvarchar(20)
)

as

set nocount on

insert into [OrderForm]
(
	[IdRepository],
	[IdCustomer],
	[RentDay],
	[Vat],
	[Discount],
	[Building]
)
values
(
	@IdRepository,
	@IdCustomer,
	@RentDay,
	@Vat,
	@Discount,
	@Building
)

select scope_identity()
go

grant execute on [dbo].[sp_OrderFormInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormUpdate]
go

create procedure [dbo].[sp_OrderFormUpdate]
(
	@Id int,
	@IdRepository int,
	@IdCustomer int,
	@RentDay datetime,
	@Vat int,
	@Discount int,
	@Building nvarchar(20)
)

as

set nocount on

update [OrderForm]
set [IdRepository] = @IdRepository,
	[IdCustomer] = @IdCustomer,
	[RentDay] = @RentDay,
	[Vat] = @Vat,
	[Discount] = @Discount,
	[Building] = @Building
where [Id] = @Id
go

grant execute on [dbo].[sp_OrderFormUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormDelete]
go

create procedure [dbo].[sp_OrderFormDelete]
(
	@Id int
)

as

set nocount on

delete from [OrderForm]
where [Id] = @Id
go

grant execute on [dbo].[sp_OrderFormDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormDeleteAllByIdCustomer]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormDeleteAllByIdCustomer]
go

create procedure [dbo].[sp_OrderFormDeleteAllByIdCustomer]
(
	@IdCustomer int
)

as

set nocount on

delete from [OrderForm]
where [IdCustomer] = @IdCustomer
go

grant execute on [dbo].[sp_OrderFormDeleteAllByIdCustomer] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormSelect]
go

create procedure [dbo].[sp_OrderFormSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[IdRepository],
	[IdCustomer],
	[RentDay],
	[Vat],
	[Discount],
	[Building]
from [OrderForm]
where [Id] = @Id
go

grant execute on [dbo].[sp_OrderFormSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormSelectAll]
go

create procedure [dbo].[sp_OrderFormSelectAll]

as

set nocount on

select [Id],
	[IdRepository],
	[IdCustomer],
	[RentDay],
	[Vat],
	[Discount],
	[Building]
from [OrderForm]
go

grant execute on [dbo].[sp_OrderFormSelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormSelectAllByIdCustomer]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormSelectAllByIdCustomer]
go

create procedure [dbo].[sp_OrderFormSelectAllByIdCustomer]
(
	@IdCustomer int
)

as

set nocount on

select [Id],
	[IdRepository],
	[IdCustomer],
	[RentDay],
	[Vat],
	[Discount],
	[Building]
from [OrderForm]
where [IdCustomer] = @IdCustomer
go

grant execute on [dbo].[sp_OrderFormSelectAllByIdCustomer] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialInsert]
go

create procedure [dbo].[sp_OrderForm_MaterialInsert]
(
	@IdOrderForm int,
	@IdMaterial int,
	@Amount int,
	@PriceByDay int,
	@Remaining int
)

as

set nocount on

insert into [OrderForm_Material]
(
	[IdOrderForm],
	[IdMaterial],
	[Amount],
	[PriceByDay],
	[Remaining]
)
values
(
	@IdOrderForm,
	@IdMaterial,
	@Amount,
	@PriceByDay,
	@Remaining
)

select scope_identity()
go

grant execute on [dbo].[sp_OrderForm_MaterialInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialUpdate]
go

create procedure [dbo].[sp_OrderForm_MaterialUpdate]
(
	@Id int,
	@IdOrderForm int,
	@IdMaterial int,
	@Amount int,
	@PriceByDay int,
	@Remaining int
)

as

set nocount on

update [OrderForm_Material]
set [IdOrderForm] = @IdOrderForm,
	[IdMaterial] = @IdMaterial,
	[Amount] = @Amount,
	[PriceByDay] = @PriceByDay,
	[Remaining] = @Remaining
where [Id] = @Id
go

grant execute on [dbo].[sp_OrderForm_MaterialUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialDelete]
go

create procedure [dbo].[sp_OrderForm_MaterialDelete]
(
	@Id int
)

as

set nocount on

delete from [OrderForm_Material]
where [Id] = @Id
go

grant execute on [dbo].[sp_OrderForm_MaterialDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialDeleteAllByIdMaterial]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialDeleteAllByIdMaterial]
go

create procedure [dbo].[sp_OrderForm_MaterialDeleteAllByIdMaterial]
(
	@IdMaterial int
)

as

set nocount on

delete from [OrderForm_Material]
where [IdMaterial] = @IdMaterial
go

grant execute on [dbo].[sp_OrderForm_MaterialDeleteAllByIdMaterial] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialDeleteAllByIdOrderForm]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialDeleteAllByIdOrderForm]
go

create procedure [dbo].[sp_OrderForm_MaterialDeleteAllByIdOrderForm]
(
	@IdOrderForm int
)

as

set nocount on

delete from [OrderForm_Material]
where [IdOrderForm] = @IdOrderForm
go

grant execute on [dbo].[sp_OrderForm_MaterialDeleteAllByIdOrderForm] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialSelect]
go

create procedure [dbo].[sp_OrderForm_MaterialSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[IdOrderForm],
	[IdMaterial],
	[Amount],
	[PriceByDay],
	[Remaining]
from [OrderForm_Material]
where [Id] = @Id
go

grant execute on [dbo].[sp_OrderForm_MaterialSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialSelectAll]
go

create procedure [dbo].[sp_OrderForm_MaterialSelectAll]

as

set nocount on

select [Id],
	[IdOrderForm],
	[IdMaterial],
	[Amount],
	[PriceByDay],
	[Remaining]
from [OrderForm_Material]
go

grant execute on [dbo].[sp_OrderForm_MaterialSelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialSelectAllByIdMaterial]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialSelectAllByIdMaterial]
go

create procedure [dbo].[sp_OrderForm_MaterialSelectAllByIdMaterial]
(
	@IdMaterial int
)

as

set nocount on

select [Id],
	[IdOrderForm],
	[IdMaterial],
	[Amount],
	[PriceByDay],
	[Remaining]
from [OrderForm_Material]
where [IdMaterial] = @IdMaterial
go

grant execute on [dbo].[sp_OrderForm_MaterialSelectAllByIdMaterial] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderForm_MaterialSelectAllByIdOrderForm]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderForm_MaterialSelectAllByIdOrderForm]
go

create procedure [dbo].[sp_OrderForm_MaterialSelectAllByIdOrderForm]
(
	@IdOrderForm int
)

as

set nocount on

select [Id],
	[IdOrderForm],
	[IdMaterial],
	[Amount],
	[PriceByDay],
	[Remaining]
from [OrderForm_Material]
where [IdOrderForm] = @IdOrderForm
go

grant execute on [dbo].[sp_OrderForm_MaterialSelectAllByIdOrderForm] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RepositoryInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_RepositoryInsert]
go

create procedure [dbo].[sp_RepositoryInsert]
(
	@Name nvarchar(50),
	@Address nvarchar(50)
)

as

set nocount on

insert into [Repository]
(
	[Name],
	[Address]
)
values
(
	@Name,
	@Address
)

select scope_identity()
go

grant execute on [dbo].[sp_RepositoryInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RepositoryUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_RepositoryUpdate]
go

create procedure [dbo].[sp_RepositoryUpdate]
(
	@Id int,
	@Name nvarchar(50),
	@Address nvarchar(50)
)

as

set nocount on

update [Repository]
set [Name] = @Name,
	[Address] = @Address
where [Id] = @Id
go

grant execute on [dbo].[sp_RepositoryUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RepositoryDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_RepositoryDelete]
go

create procedure [dbo].[sp_RepositoryDelete]
(
	@Id int
)

as

set nocount on

delete from [Repository]
where [Id] = @Id
go

grant execute on [dbo].[sp_RepositoryDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RepositorySelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_RepositorySelect]
go

create procedure [dbo].[sp_RepositorySelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[Name],
	[Address]
from [Repository]
where [Id] = @Id
go

grant execute on [dbo].[sp_RepositorySelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RepositorySelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_RepositorySelectAll]
go

create procedure [dbo].[sp_RepositorySelectAll]

as

set nocount on

select [Id],
	[Name],
	[Address]
from [Repository]
go

grant execute on [dbo].[sp_RepositorySelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialInsert]
go

create procedure [dbo].[sp_Repository_MaterialInsert]
(
	@IdMaterial int,
	@IdRepository int,
	@Amount int,
	@Remaining int
)

as

set nocount on

insert into [Repository_Material]
(
	[IdMaterial],
	[IdRepository],
	[Amount],
	[Remaining]
)
values
(
	@IdMaterial,
	@IdRepository,
	@Amount,
	@Remaining
)

select scope_identity()
go

grant execute on [dbo].[sp_Repository_MaterialInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialUpdate]
go

create procedure [dbo].[sp_Repository_MaterialUpdate]
(
	@Id int,
	@IdMaterial int,
	@IdRepository int,
	@Amount int,
	@Remaining int
)

as

set nocount on

update [Repository_Material]
set [IdMaterial] = @IdMaterial,
	[IdRepository] = @IdRepository,
	[Amount] = @Amount,
	[Remaining] = @Remaining
where [Id] = @Id
go

grant execute on [dbo].[sp_Repository_MaterialUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialDelete]
go

create procedure [dbo].[sp_Repository_MaterialDelete]
(
	@Id int
)

as

set nocount on

delete from [Repository_Material]
where [Id] = @Id
go

grant execute on [dbo].[sp_Repository_MaterialDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialDeleteAllByIdMaterial]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialDeleteAllByIdMaterial]
go

create procedure [dbo].[sp_Repository_MaterialDeleteAllByIdMaterial]
(
	@IdMaterial int
)

as

set nocount on

delete from [Repository_Material]
where [IdMaterial] = @IdMaterial
go

grant execute on [dbo].[sp_Repository_MaterialDeleteAllByIdMaterial] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialDeleteAllByIdRepository]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialDeleteAllByIdRepository]
go

create procedure [dbo].[sp_Repository_MaterialDeleteAllByIdRepository]
(
	@IdRepository int
)

as

set nocount on

delete from [Repository_Material]
where [IdRepository] = @IdRepository
go

grant execute on [dbo].[sp_Repository_MaterialDeleteAllByIdRepository] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialSelect]
go

create procedure [dbo].[sp_Repository_MaterialSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[IdMaterial],
	[IdRepository],
	[Amount],
	[Remaining]
from [Repository_Material]
where [Id] = @Id
go

grant execute on [dbo].[sp_Repository_MaterialSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialSelectAll]
go

create procedure [dbo].[sp_Repository_MaterialSelectAll]

as

set nocount on

select [Id],
	[IdMaterial],
	[IdRepository],
	[Amount],
	[Remaining]
from [Repository_Material]
go

grant execute on [dbo].[sp_Repository_MaterialSelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialSelectAllByIdMaterial]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialSelectAllByIdMaterial]
go

create procedure [dbo].[sp_Repository_MaterialSelectAllByIdMaterial]
(
	@IdMaterial int
)

as

set nocount on

select [Id],
	[IdMaterial],
	[IdRepository],
	[Amount],
	[Remaining]
from [Repository_Material]
where [IdMaterial] = @IdMaterial
go

grant execute on [dbo].[sp_Repository_MaterialSelectAllByIdMaterial] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialSelectAllByIdRepository]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialSelectAllByIdRepository]
go

create procedure [dbo].[sp_Repository_MaterialSelectAllByIdRepository]
(
	@IdRepository int
)

as

set nocount on

select [Id],
	[IdMaterial],
	[IdRepository],
	[Amount],
	[Remaining]
from [Repository_Material]
where [IdRepository] = @IdRepository
go

grant execute on [dbo].[sp_Repository_MaterialSelectAllByIdRepository] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UnitInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UnitInsert]
go

create procedure [dbo].[sp_UnitInsert]
(
	@Id int,
	@UnitName nvarchar(20),
	@Note nvarchar(50)
)

as

set nocount on

insert into [Unit]
(
	[Id],
	[UnitName],
	[Note]
)
values
(
	@Id,
	@UnitName,
	@Note
)
go

grant execute on [dbo].[sp_UnitInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UnitUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UnitUpdate]
go

create procedure [dbo].[sp_UnitUpdate]
(
	@Id int,
	@UnitName nvarchar(20),
	@Note nvarchar(50)
)

as

set nocount on

update [Unit]
set [UnitName] = @UnitName,
	[Note] = @Note
where [Id] = @Id
go

grant execute on [dbo].[sp_UnitUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UnitDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UnitDelete]
go

create procedure [dbo].[sp_UnitDelete]
(
	@Id int
)

as

set nocount on

delete from [Unit]
where [Id] = @Id
go

grant execute on [dbo].[sp_UnitDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UnitSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UnitSelect]
go

create procedure [dbo].[sp_UnitSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[UnitName],
	[Note]
from [Unit]
where [Id] = @Id
go

grant execute on [dbo].[sp_UnitSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UnitSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UnitSelectAll]
go

create procedure [dbo].[sp_UnitSelectAll]

as

set nocount on

select [Id],
	[UnitName],
	[Note]
from [Unit]
go

grant execute on [dbo].[sp_UnitSelectAll] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UserInsert]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UserInsert]
go

create procedure [dbo].[sp_UserInsert]
(
	@UserName varchar(20),
	@Password varchar(32),
	@Type varchar(10),
	@Email nchar(30),
	@Active bit,
	@Address nvarchar(50),
	@Name nvarchar(30)
)

as

set nocount on

insert into [User]
(
	[UserName],
	[Password],
	[Type],
	[Email],
	[Active],
	[Address],
	[Name]
)
values
(
	@UserName,
	@Password,
	@Type,
	@Email,
	@Active,
	@Address,
	@Name
)

select scope_identity()
go

grant execute on [dbo].[sp_UserInsert] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UserUpdate]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UserUpdate]
go

create procedure [dbo].[sp_UserUpdate]
(
	@Id int,
	@UserName varchar(20),
	@Password varchar(32),
	@Type varchar(10),
	@Email nchar(30),
	@Active bit,
	@Address nvarchar(50),
	@Name nvarchar(30)
)

as

set nocount on

update [User]
set [UserName] = @UserName,
	[Password] = @Password,
	[Type] = @Type,
	[Email] = @Email,
	[Active] = @Active,
	[Address] = @Address,
	[Name] = @Name
where [Id] = @Id
go

grant execute on [dbo].[sp_UserUpdate] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UserDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UserDelete]
go

create procedure [dbo].[sp_UserDelete]
(
	@Id int
)

as

set nocount on

delete from [User]
where [Id] = @Id
go

grant execute on [dbo].[sp_UserDelete] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UserSelect]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UserSelect]
go

create procedure [dbo].[sp_UserSelect]
(
	@Id int
)

as

set nocount on

select [Id],
	[UserName],
	[Password],
	[Type],
	[Email],
	[Active],
	[Address],
	[Name]
from [User]
where [Id] = @Id
go

grant execute on [dbo].[sp_UserSelect] to [sa]
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UserSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UserSelectAll]
go

create procedure [dbo].[sp_UserSelectAll]

as

set nocount on

select [Id],
	[UserName],
	[Password],
	[Type],
	[Email],
	[Active],
	[Address],
	[Name]
from [User]
go

grant execute on [dbo].[sp_UserSelectAll] to [sa]
go
